package com.qingcheng.dao;

import com.qingcheng.pojo.order.CategoryReport;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import tk.mybatis.mapper.common.Mapper;

import java.time.LocalDate;
import java.util.List;
import java.util.Map;

public interface CategoryReportMapper extends Mapper<CategoryReport> {

    @Select("SELECT " +
            "  `category_id1` categoryId1," +
            "  `category_id2` categoryId2," +
            "  `category_id3` categoryId3," +
            "  DATE_FORMAT(o.`pay_time`, '%Y-%m-%d') countDate," +
            "  SUM(oi.`num`) num," +
            "  SUM(oi.`pay_money`) money " +
            "FROM" +
            "  tb_order o," +
            "  tb_order_item oi " +
            "WHERE o.`id` = oi.`order_id` " +
            "  AND o.`pay_status` = '1' " +
            "  AND o.`is_delete` = '0' " +
            "  AND DATE_FORMAT(o.`pay_time`, '%Y-%m-%d') = #{date} " +
            "GROUP BY `category_id1`," +
            "  `category_id2`," +
            "  `category_id3`," +
            "  DATE_FORMAT(o.`pay_time`, '%Y-%m-%d')")
    List<CategoryReport> categoryReport(@Param("date") LocalDate date);


    @Select("SELECT " +
            "  cr.`category_id1` categoryId1," +
            "  vc.name categoryName," +
            "  SUM(cr.`num`) num," +
            "  SUM(cr.`money`) money " +
            "FROM" +
            "  `tb_category_report` cr," +
            "  v_category vc " +
            "WHERE cr.`category_id1` = vc.id " +
            "  AND cr.`count_date` >= #{beginDate} " +
            "  AND cr.`count_date` <= #{endDate} " +
            "GROUP BY cr.`category_id1` ")
    List<Map> category1Count(@Param("beginDate") String beginDate,@Param("endDate") String endDate);
}
